﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ELearning.Models;

namespace ELearning.DAL
{
    /// <summary>
    /// ELevel数据访问层
    /// </summary>
	public partial class EDictionaryRepository : BaseRepository<EDictionary>
    {
        /// <summary>
        /// 根据字典id获取它以及子节点
        /// </summary>
        /// <param name="id">等级id</param>
        public List<EDictionary> GetDicListById(Guid id)
        {
            var sql = $@";
  WITH cte AS
                (
                    SELECT  *
                    FROM   EDictionary
                    WHERE  ID =  '{id}'
                    UNION ALL
                    SELECT A.*
                    FROM   EDictionary A
                    JOIN   cte B ON A.ParentID = B.ID  AND a.IsDelete = 0
                )
            --返回自己和所有的子节点
            SELECT *  FROM   cte   ";
            var modelList = nContext.Database.SqlQuery<EDictionary>(sql).ToList();
            return modelList;
        }

        /// <summary>
        /// 根据字典id获取它以及子节点
        /// </summary>
        /// <param name="id">等级id</param>
        public List<EDictionary> GetDicInParent(string code)
        {
            var sql = $@";
                     WITH cte AS
                        (
                            SELECT  *
                            FROM   EDictionary
                            WHERE  TypeCode =  '{code}'
                            UNION ALL
                            SELECT A.*
                            FROM   EDictionary A
                            JOIN   cte B ON A.ParentID = B.ID  AND a.IsDelete = 0
                        )
                    --返回所有的子节点
                    SELECT *  FROM   cte  
                    order by Sort ";
            var modelList = nContext.Database.SqlQuery<EDictionary>(sql).ToList();
            return modelList;
        }

        /// <summary>
        /// 根据字典id获取子节点
        /// </summary>
        /// <param name="id">等级id</param>
        public List<EDictionary> GetDicNoParent(string code)
        {
            var sql = $@";
                     WITH cte AS
                        (
                            SELECT  *
                            FROM   EDictionary
                            WHERE  TypeCode =  '{code}'
                            UNION ALL
                            SELECT A.*
                            FROM   EDictionary A
                            JOIN   cte B ON A.ParentID = B.ID  AND a.IsDelete = 0
                        )
                    --返回所有的子节点
                    SELECT *  FROM   cte   where TypeCode !=  '{code}' 
                    order by Sort ";
            var modelList = nContext.Database.SqlQuery<EDictionary>(sql).ToList();
            return modelList;
        }

        /// <summary>
        /// 根据子节点获取所有父节点
        /// </summary>
        /// <param name="id">等级id</param>
        public List<EDictionary> GetDicParent(string code)
        {
            var sql = $@";
		                    WITH cte AS
		                    (
		                    SELECT  *
		                    FROM   EDictionary
		                    WHERE  TypeCode =  '{code}'
		                    UNION ALL
		                    SELECT A.*
		                    FROM   EDictionary A
		                    JOIN   cte B ON B.ParentID = A.ID  AND A.IsDelete = 0
		                    )
		                    SELECT *  FROM   cte   ";
            //where ParentID!='00000000-0000-0000-0000-000000000000'";
            var modelList = nContext.Database.SqlQuery<EDictionary>(sql).ToList();
            return modelList;
        }

        /// <summary>
        /// 根据多个以逗号分隔的父节点获取所有子节点
        /// </summary>
        /// <param name="id">等级id</param>
        public List<string> GetDicChildAll(string codeArr)
        {
            var sql = $@";
                                WITH cte AS
                                (
                                SELECT  *
                                FROM   EDictionary
                                WHERE  TypeCode in('{codeArr}')
                                UNION ALL
                                SELECT A.*
                                FROM   EDictionary A
                                JOIN   cte B ON A.ParentID = B.ID  AND A.IsDelete = 0
                                )
                                --返回所有的子节点
                                SELECT distinct typecode  FROM   cte;   ";
            var modelList = nContext.Database.SqlQuery<string>(sql).ToList();
            return modelList;
        }

        /// <summary>
        /// 字典数据删除，逻辑：被使用的字典不可删除
        /// </summary>
        /// <param name="id">字典ID</param>
        public int GetDicUsedCount(string dicID)
        {
            var sql = $@";                                
                        with aa as(
                        select count(1) UsedCount from ECourseSeries
                        where SID='{dicID}' and IsDelete=0
                        union all 
                        select count(1) UsedCount from EProduct
                        where SubjectID='{dicID}' and IsDelete=0
                        union all 
                        select count(1) UsedCount from EQuestion a,[EDictionary] b 
                        where a.IsDelete=0 and b.ID='{dicID}'
                        and a.SubjectCode=b.TypeCode
                        union all 
                        select count(1) UsedCount from [EExam] a,[EDictionary] b 
                        where a.IsDelete=0 and b.ID='{dicID}'
                        and a.SubjectCode like '%'+b.TypeCode+'%'
                        union all 
                        select count(1) UsedCount from [EPaper] a,[EDictionary] b 
                        where a.IsDelete=0 and b.ID='{dicID}'
                        and a.SubjectCode like '%'+b.TypeCode+'%'
                        union all 
                        select count(1) UsedCount from [EPaperRule] a,[EDictionary] b 
                        where a.IsDelete=0 and b.ID='{dicID}'
                        and a.SubjectCode like '%'+b.TypeCode+'%'
                        )
                        select sum(UsedCount) UsedCount from aa;   ";
            var modelList = nContext.Database.SqlQuery<int>(sql).FirstOrDefault();
            return modelList;
        }
    }


}



